import random
import time
from datetime import datetime
import requests
from pyquery import PyQuery as pq
from common import p, getFileContent, putFileContent
import os
from Db.Db import Db
import json

#爬取安居客的房屋数据 本次主要引入的主要技术点
#1.数据库信息的储存，可采集储存百万级别数据（txt储存不够用）
#2.增删改查的封装实现+批量插入的基础封装
#3.引入pquery解析库，更方便的场景
#4.掌握数组归并，提升效率（无需每个单独插入）
#5.读取属性值（属性值里面很多关键信息） 这里以店长标签为例子
#6.掌握python清洗数据库的初级方法，使用原生SQL语句查询数据
#7.列表推导式的强化（只读取字符串里面的数字）
#8.数据库的原生查询方法（非常有用，复杂查询直接构造效率更高）
#9.减弱验证码的风控条件（采用拟真访问，限制请求频率）
#10.复杂条件查询（条件>=<的复合条件查询）

cookies = {
    'aQQ_ajkguid': '5DB46AB3-33C6-4F2C-AE0D-3E4CDD3ACCA1',
    'sessid': 'A4C24D2E-5960-4C43-A7C0-B859C456024A',
    'ajk-appVersion': '',
    'ctid': '52',
    'id58': 'CrIfoWaarIwXo08SdfMXAg==',
    'twe': '2',
    'xxzlclientid': '280d0551-62e1-480b-bdb1-1721412746180',
    'xxzlxxid': 'pfmxEhzCTI+a5vYxhqYY7DzYERPkkZ8bJiy85Vk/Z8C7kz+uvKs/dyxXcsjaRiMm8ikk',
    'fzq_h': 'b95ffb5680b1da4e99f6d2f3538ee0e4_1721412762208_5537e09b93cb497299f75d4de12b31a0_3746202085',
    'lps': 'https%3A%2F%2Fhuizhou.zu.anjuke.com%2F%3Ffrom%3Desf_list%7Chttps%3A%2F%2Fhuizhou.anjuke.com%2F',
    'cmctid': '722',
    'wmda_uuid': '8f3bf83727a510ac0ff1b13726257934',
    'wmda_new_uuid': '1',
    'wmda_visited_projects': '%3B6289197098934',
    '58tj_uuid': '59a98c45-79da-47c9-8f4c-4920e299df4f',
    'new_uv': '1',
    '_ga': 'GA1.2.1161557079.1721414462',
    '_gid': 'GA1.2.928508505.1721414462',
    'als': '0',
    '_ga_DYBJHZFBX2': 'GS1.2.1721414462.1.1.1721414467.0.0.0',
    'wmda_session_id_6289197098934': '1721416583658-52e6f3bb-bb46-60e9',
    'obtain_by': '2',
    'ajkAuthTicket': 'TT=4de0e3544f9035f8d065f55a1fe32348&TS=1721416649482&PBODY=Q_sqpgHQNj1B_XGIMzZB8XeeTITbHOsjOlobdrpWRFgKQRmsxKA2vR4CSjPuhofSX0O5k6NyIW_5FYaM7GFhgVA9sVEpDC3-1M4OJz1fUHiTVoQX04MtOmLlVyiQtn4YmJrETiNcQ2lsTznwBLoKqSmHJ8PA1gcDBnDQgwWwtUU&VER=2&CUID=gnDLxZjxenYTacvKAewBFxFWXp57TMBP',
    'ajk_member_verify': '%2FkGbBelmZwq3eRu4Rz8RKQ5Klb9ZryfV9zW2gzoByLg%3D',
    'ajk_member_verify2': 'MjkyNzgyNTE3fHJJaDRzMUp8MQ%3D%3D',
    'ajk_member_id': '292782517',
    'xxzlbbid': 'pfmbM3wxMDMyNXwxLjkuMHwxNzIxNDE2NjQ4OTkxODA4OTMyfGtjZGhiR0VtQ1JLWTRLZ1J4a2JiOEJ4Z2xsaG9VaVFyY1VyK3dBaXM3dk09fGUzZWY4MGY5MWUwMTJmN2Y4MGZhYWUwOGYzNDdkNjllXzE3MjE0MTY2NTEzNDdfYWMyMzI4YWZlOGE3NDJlZmI3N2RkZGRlNmVhMzRjOWVfMzc0NjIwMjA4NXxkYjllZWY4N2MxYmQ0OTEyODJkNDJiMTUwNGFhYzExM18xNzIxNDE2NjQ4NDkwXzI1NA==',
    'xxzl_cid': '5e863442cc3b40b9a928d9e766ab7ab2',
    'xxzl_deviceid': '04ikTdIRShhgphJUNCCoeP0Jdu4VPnYE0mn0wxZ9+fiGPPOdHsz23ttwvr3Utxpd',
}

headers = {
    'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
    'accept-language': 'zh-CN,zh;q=0.9',
    'cache-control': 'no-cache',
    # 'cookie': 'aQQ_ajkguid=5DB46AB3-33C6-4F2C-AE0D-3E4CDD3ACCA1; sessid=A4C24D2E-5960-4C43-A7C0-B859C456024A; ajk-appVersion=; ctid=52; id58=CrIfoWaarIwXo08SdfMXAg==; twe=2; xxzlclientid=280d0551-62e1-480b-bdb1-1721412746180; xxzlxxid=pfmxEhzCTI+a5vYxhqYY7DzYERPkkZ8bJiy85Vk/Z8C7kz+uvKs/dyxXcsjaRiMm8ikk; fzq_h=b95ffb5680b1da4e99f6d2f3538ee0e4_1721412762208_5537e09b93cb497299f75d4de12b31a0_3746202085; lps=https%3A%2F%2Fhuizhou.zu.anjuke.com%2F%3Ffrom%3Desf_list%7Chttps%3A%2F%2Fhuizhou.anjuke.com%2F; cmctid=722; wmda_uuid=8f3bf83727a510ac0ff1b13726257934; wmda_new_uuid=1; wmda_visited_projects=%3B6289197098934; 58tj_uuid=59a98c45-79da-47c9-8f4c-4920e299df4f; new_uv=1; _ga=GA1.2.1161557079.1721414462; _gid=GA1.2.928508505.1721414462; als=0; _ga_DYBJHZFBX2=GS1.2.1721414462.1.1.1721414467.0.0.0; wmda_session_id_6289197098934=1721416583658-52e6f3bb-bb46-60e9; obtain_by=2; ajkAuthTicket=TT=4de0e3544f9035f8d065f55a1fe32348&TS=1721416649482&PBODY=Q_sqpgHQNj1B_XGIMzZB8XeeTITbHOsjOlobdrpWRFgKQRmsxKA2vR4CSjPuhofSX0O5k6NyIW_5FYaM7GFhgVA9sVEpDC3-1M4OJz1fUHiTVoQX04MtOmLlVyiQtn4YmJrETiNcQ2lsTznwBLoKqSmHJ8PA1gcDBnDQgwWwtUU&VER=2&CUID=gnDLxZjxenYTacvKAewBFxFWXp57TMBP; ajk_member_verify=%2FkGbBelmZwq3eRu4Rz8RKQ5Klb9ZryfV9zW2gzoByLg%3D; ajk_member_verify2=MjkyNzgyNTE3fHJJaDRzMUp8MQ%3D%3D; ajk_member_id=292782517; xxzlbbid=pfmbM3wxMDMyNXwxLjkuMHwxNzIxNDE2NjQ4OTkxODA4OTMyfGtjZGhiR0VtQ1JLWTRLZ1J4a2JiOEJ4Z2xsaG9VaVFyY1VyK3dBaXM3dk09fGUzZWY4MGY5MWUwMTJmN2Y4MGZhYWUwOGYzNDdkNjllXzE3MjE0MTY2NTEzNDdfYWMyMzI4YWZlOGE3NDJlZmI3N2RkZGRlNmVhMzRjOWVfMzc0NjIwMjA4NXxkYjllZWY4N2MxYmQ0OTEyODJkNDJiMTUwNGFhYzExM18xNzIxNDE2NjQ4NDkwXzI1NA==; xxzl_cid=5e863442cc3b40b9a928d9e766ab7ab2; xxzl_deviceid=04ikTdIRShhgphJUNCCoeP0Jdu4VPnYE0mn0wxZ9+fiGPPOdHsz23ttwvr3Utxpd',
    'pragma': 'no-cache',
    'priority': 'u=0, i',
    'sec-ch-ua': '"Not/A)Brand";v="8", "Chromium";v="126", "Google Chrome";v="126"',
    'sec-ch-ua-mobile': '?0',
    'sec-ch-ua-platform': '"Windows"',
    'sec-fetch-dest': 'document',
    'sec-fetch-mode': 'navigate',
    'sec-fetch-site': 'none',
    'sec-fetch-user': '?1',
    'upgrade-insecure-requests': '1',
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36',
}

def fangzi(page):

    response = requests.get('https://huizhou.anjuke.com/sale/p{}-rd1/'.format(page), cookies=cookies, headers=headers)

    content = pq(response.text)
    cont = pq(content)
    info = cont(".list .property a").items()
    # if len(info) ==0:
    #     p("验证异常")

    now = datetime.now()
    formatted_time = now.strftime('%Y-%m-%d %H:%M:%S')
    arrs = []
    for fang in info:
        # 获取房子的标题信息
        fname = fang("h3").text()

        des = fang.attr("data-ep")
        darr = json.loads(des)

        # fsid = darr['exposure']['label_name'] #读取属性值
        fsize = fang(".property-content-info-text").text()
        fbrand = fang(".property-content-info-comm-name").text()
        faddress = fang(".property-content-info-comm-address").text()
        ftag = fang(".property-content-info-tag").text()
        fprice = fang(".property-price").text()
        junjia =''.join([char for char in fang(".property-price-average").text() if char.isdigit()])

        arr = {
            'fname': fname,
            'sid' : '',
            'fsize': fsize,
            'fbrand': fbrand,
            'faddress': faddress,
            'ftag': ftag,
            'fprice': fprice,
            'create_at': formatted_time,
            'junjia': junjia
        }

        arrs.append(arr)

    res = Db('mysql1').table('x5m').createAll(arrs)


#数据库操作部分 这里使用mysql储存房源（因为可能要要用到）

def  caiji () :
    for i in range(1, 51):
        fangzi(i)
        sec = random.randint(3, 7)
        time.sleep(sec)
        p(f"已经抓取{i}页", 2)

def  cleanDt() :
    sql = "SELECT fname, COUNT(*) AS ct FROM x5m GROUP BY fname HAVING ct > 1;"
    infos =Db('mysql1').table('x5m').rawSel(sql)
    for  info in infos :
        twos = Db('mysql1').table('x5m').where('fname',info[0]).get() #读取全部相同的数据
        id1 = twos[0][0]
        delres = Db('mysql1').table('x5m').where('fname',info[0]).where('id','>',id1).delete()
        p(f'已经清理{info[0]}',2)

def tongji() :
    sql = "select count(*),sum(junjia) from x5m"
    infos = Db('mysql1').table('x5m').rawSel(sql)
    ave = round(float(infos[0][1])/float(infos[0][0]),2)
    p(f"当前总共有{infos[0][0]}套房在售,房子平均价格{ave}")


caiji()


